package com.tuanzi.loan.business.repository.statistics.impl;

import com.google.common.collect.Lists;
import com.tuanzi.loan.business.entity.statistics.ImeiRecord;
import com.tuanzi.loan.business.entity.statistics.ImeiStat;
import com.tuanzi.loan.business.repository.statistics.ImeiStatRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.statistics.imei.ImeiStatRequest;
import com.tuanzi.loan.vo.response.statistics.imei.ImeiStatResponse;
import org.jooq.impl.DSL;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Repository;

import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

import static com.tuanzi.loan.generated.jooq.loan.Tables.COMM_PRODUCT_PRDID;
import static com.tuanzi.loan.generated.jooq.loan.tables.CommProductChannel.COMM_PRODUCT_CHANNEL;
import static com.tuanzi.loan.generated.jooq.stat.tables.LoanImeiStat.LOAN_IMEI_STAT;

@Repository
public class ImeiStatRepositoryImpl extends JOOQBaseRepositoryPlus implements ImeiStatRepository {

    @Override
    protected TableImpl getTable() {
        return LOAN_IMEI_STAT;
    }

    @Override
    public Page<ImeiStatResponse> list(ImeiStatRequest searchRequest, Pageable pageable) {
        List<Integer> productIdList = searchRequest.getProductIdList();
        List<Integer> channelIdList = searchRequest.getChannelIdList();
        Date createStartTime = searchRequest.getCreateStartTime();
        Date createEndTime = searchRequest.getCreateEndTime();
        fields(LOAN_IMEI_STAT.ID, LOAN_IMEI_STAT.CREATE_DATE, LOAN_IMEI_STAT.PRD_ID, LOAN_IMEI_STAT.CHANNEL_CODE, LOAN_IMEI_STAT.REGISTER_COUNT, LOAN_IMEI_STAT.LOGIN_COUNT,
                COMM_PRODUCT_PRDID.NAME, COMM_PRODUCT_CHANNEL.CHANNEL_NAME);
        leftJoin(COMM_PRODUCT_PRDID, COMM_PRODUCT_PRDID.PRDID.eq(LOAN_IMEI_STAT.PRD_ID));
        leftJoin(COMM_PRODUCT_CHANNEL, COMM_PRODUCT_CHANNEL.CHANNEL_CODE.eq(LOAN_IMEI_STAT.CHANNEL_CODE));
        between(LOAN_IMEI_STAT.CREATE_DATE, createStartTime, createEndTime);
        in(LOAN_IMEI_STAT.PRD_ID, productIdList);
        in(LOAN_IMEI_STAT.CHANNEL_CODE, channelIdList);
        orderBy(LOAN_IMEI_STAT.CREATE_DATE, Sort.Direction.DESC);

        Page<ImeiStatResponse> search = search(pageable, record -> {
            ImeiStatResponse response = new ImeiStatResponse();
            response.setId(record.get(LOAN_IMEI_STAT.ID));
            response.setCreateDate(record.get(LOAN_IMEI_STAT.CREATE_DATE));
            response.setPrdId(record.get(LOAN_IMEI_STAT.PRD_ID));
            response.setProductName(record.get(COMM_PRODUCT_PRDID.NAME));
            response.setChannel(record.get(LOAN_IMEI_STAT.CHANNEL_CODE));
            response.setChannelName(record.get(COMM_PRODUCT_CHANNEL.CHANNEL_NAME));
            response.setRegisterCount(record.get(LOAN_IMEI_STAT.REGISTER_COUNT));
            response.setLoginCount(record.get(LOAN_IMEI_STAT.LOGIN_COUNT));
            return response;
        });
        return search;
    }

    @Override
    public void insert(ImeiStat imeiStat) {
        dslContext.insertInto(LOAN_IMEI_STAT, LOAN_IMEI_STAT.CREATE_DATE, LOAN_IMEI_STAT.PRD_ID, LOAN_IMEI_STAT.CHANNEL_CODE, LOAN_IMEI_STAT.REGISTER_COUNT, LOAN_IMEI_STAT.LOGIN_COUNT)
                .values(new java.sql.Date(imeiStat.getCreateDate().getTime()), imeiStat.getPrdId(), imeiStat.getChannelCode(), imeiStat.getRegisterCount(), imeiStat.getLoginCount()).execute();
    }

    @Override
    public Integer getRegisterCount(ImeiRecord record) {
        List<Object> parameters = Lists.newArrayList();
        parameters.add(new java.sql.Date(record.getCreateDate().getTime()));
        parameters.add(record.getPrdId());
        parameters.add(new java.sql.Date(record.getCreateDate().getTime()));
        parameters.add(record.getPrdId());
        parameters.add(record.getChannelCode());
        parameters.add(DSL.list(record.getImeiNumbers().stream().map(DSL::val).collect(Collectors.toList())));
        return dslContext.fetchOne(
                "SELECT COUNT(DISTINCT(user_id)) AS '注册人数' FROM loan_device_info AS a INNER JOIN common_app_register_record AS b ON a.mobnum = b.mobnum " +
                        "WHERE b.cdate = ? AND b.prdid = ? AND  a.create_date = ? AND a.product_id = ? AND a.channel_code = ? AND MD5(a.idfa_imei) IN(?)"
                , parameters.toArray()).into(Integer.class);
    }

    @Override
    public Integer getLoginCount(ImeiRecord record) {
        List<Object> parameters = Lists.newArrayList();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(record.getCreateDate());
        calendar.add(Calendar.DAY_OF_MONTH, 1);
        parameters.add(new java.sql.Date(record.getCreateDate().getTime()));
        parameters.add(new java.sql.Date(calendar.getTime().getTime()));
        parameters.add(record.getPrdId());
        parameters.add(record.getChannelCode());
        parameters.add(record.getPrdId());
        parameters.add(record.getChannelCode());
        parameters.add(DSL.list(record.getImeiNumbers().stream().map(DSL::val).collect(Collectors.toList())));
        return dslContext.fetchOne(
                "SELECT COUNT(DISTINCT(userid)) AS '登录人数' FROM user_login_record WHERE ctime >= ? AND ctime < ? AND productid = ? AND channel = ? AND userid IN (" +
                        "SELECT user_id FROM loan_device_info WHERE product_id = ? AND channel_code = ? AND MD5(idfa_imei) IN(?))"
                , parameters.toArray()).into(Integer.class);
    }

    @Override
    public void deleteById(Long id) {
        dslContext.deleteFrom(LOAN_IMEI_STAT).where(LOAN_IMEI_STAT.ID.eq(id)).execute();
    }

    @Override
    public List<String> getRegisterList(ImeiRecord record) {
        List<Object> parameters = Lists.newArrayList();
        parameters.add(new java.sql.Date(record.getCreateDate().getTime()));
        parameters.add(record.getPrdId());
        parameters.add(new java.sql.Date(record.getCreateDate().getTime()));
        parameters.add(record.getPrdId());
        parameters.add(record.getChannelCode());
        parameters.add(DSL.list(record.getImeiNumbers().stream().map(DSL::val).collect(Collectors.toList())));
        return dslContext.fetch(
                "SELECT DISTINCT(MD5(a.idfa_imei)) FROM loan_device_info AS a INNER JOIN common_app_register_record AS b ON a.mobnum = b.mobnum " +
                        "WHERE b.cdate = ? AND b.prdid = ? AND  a.create_date = ? AND a.product_id = ? AND a.channel_code = ? AND MD5(a.idfa_imei) IN(?)"
                , parameters.toArray()).into(String.class);
    }

    @Override
    public List<String> getLoginList(ImeiRecord record) {
        List<Object> parameters = Lists.newArrayList();
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(record.getCreateDate());
        calendar.add(Calendar.DAY_OF_MONTH, 1);
        parameters.add(new java.sql.Date(record.getCreateDate().getTime()));
        parameters.add(new java.sql.Date(calendar.getTime().getTime()));
        parameters.add(record.getPrdId());
        parameters.add(record.getChannelCode());
        parameters.add(record.getPrdId());
        parameters.add(record.getChannelCode());
        parameters.add(DSL.list(record.getImeiNumbers().stream().map(DSL::val).collect(Collectors.toList())));
        List<Long> userIds = dslContext.fetch(
                "SELECT DISTINCT(userid) FROM user_login_record WHERE ctime >= ? AND ctime < ? AND productid = ? AND channel = ? AND userid IN (" +
                        "SELECT user_id FROM loan_device_info WHERE product_id = ? AND channel_code = ? AND MD5(idfa_imei) IN(?))"
                , parameters.toArray()).into(Long.class);

        List<Object> otherParams = Lists.newArrayList();
        otherParams.add(DSL.list(userIds.stream().map(DSL::val).collect(Collectors.toList())));
        return dslContext.fetch(
                "SELECT MD5(idfa_imei) FROM loan_device_info WHERE user_id IN(?)",
                otherParams.toArray()).into(String.class);
    }
}
